﻿using sMES_Base;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using sMES_Base.service;
using System.Data;
using load_applied_acc_info_get.Models;
using load_applied_acc_info_get_C.ORM;

namespace load_applied_acc_info_get
{
    /// <summary>
	/// 元件[load_applied_acc_info_get][模製具模組][設備模具上模清單]
    /// <auth>DGW</auth><time>2018/11/23 上午 10:58:42</time>
    /// <description></description>
    /// <list>
    ///     <modify>
    ///         <auth></auth><mantis></mantis><time></time>
    ///         <description></description>
    ///     </modify>
    /// </list>
    /// </summary>
    public class load_applied_acc_info_get_utility : BaseUtility
    {
        public load_applied_acc_info_get_utility(ref BaseService CurrentService) : base(ref CurrentService)
        {
        }

        /// <summary>
        /// 載入這個設備類別可上模清單
        /// TBLEMSACCESSORYSTATE 模治具现况表
        /// TBLEQPACCSTATECONTROL 模具控制項定義
        /// TBLEQPACCESSORYBASIS 模治具基本资料表
        /// TBLEQPEQUIPMENTTYPEACCCATEGORY 设备模治具分类
        /// </summary>
        /// <param name="conten"></param>
        /// <returns></returns>
        public DataTable load_applied_acc_info_detail(REQ_load_applied_acc_info_get conten)
        {
            DataTable OutDataTable = new DataTable();
            bool is_auto_inout = get_auto_inout_val();  //自動出入庫
            StringBuilder _sql = new StringBuilder();
            _sql.Append(" SELECT A.AccessoryNo AS \"AccessoryNo\", ");
            _sql.Append("        A.AccessoryType AS \"AccessoryType\", ");
            _sql.Append("        D.AccessoryCategory AS \"AccessoryCategory\", ");
            _sql.Append("        A.AccessoryVersion AS \"AccessoryVersion\", ");
            _sql.Append("        A.LocatorNo AS \"LocatorNo\", ");
            _sql.Append("        A.ACCTotalUsedQTY AS \"ACCTotalUsedQTY\", ");
            _sql.Append("        A.AccumulateQTY AS \"AccumulateQTY\", ");
            //20190129 modify by Dustdusk for M#: 把計算從程式改成SQL
            _sql.Append("        CASE WHEN C.LifeManage is not null AND C.LifeManage != 0 AND C.ScrapType = 1 THEN C.RepairCycle - AccumulateQTY ");
            _sql.Append("             ELSE NULL END AS \"ResidualLife\", ");
            _sql.Append("        CASE WHEN C.LifeManage is not null AND C.LifeManage != 0 AND C.ScrapType = 1 THEN C.ScrapLife - ACCTotalUsedQTY ");
            _sql.Append("             ELSE NULL END AS \"TotalResidualLife\", ");
            _sql.Append("        C.LifeManage AS \"LifeManage\", ");
            _sql.Append("        C.ScrapType AS \"ScrapType\", ");
            _sql.Append("        C.RepairCycle AS \"RepairCycle\", ");
            _sql.Append("        C.ScrapLife AS \"ScrapLife\", ");
            _sql.Append("        D.AccessoryName AS \"AccessoryName\", ");
            // 20210426 Modify by Zeke for 85272: 模具上模報錯：TBLEMSEQPACCSTATE重覆索引 [金名山]
            // 20220208 Modify by 翰鴻 for M#102969
            _sql.Append("        A.ACCSerialNo AS \"ACCSerialNo\", ");
            //_sql.Append(" A.TempLocatorNo, ");
            _sql.Append("CASE WHEN F.ACCESSORYNO IS NOT NULL THEN 1 ELSE 0 END CHANGETOOL, ");
            //20220308 add by 翰鴻 for M#102577
            _sql.Append("       A.tempLocatorNo \"TempLocatorNo\",   ");
            _sql.Append("       A.AcLoadTime \"AcLoadTime\",      ");
            _sql.Append("       A.AcUnLoadTime \"AcUnLoadTime\",    ");
            _sql.Append("       G.LoadTools \"LoadTools\",       ");
            _sql.Append("       G.UnloadTools \"UnloadTools\",     ");
            _sql.Append("       G.LoadTime \"LoadTime\",       ");
            _sql.Append("       G.UnloadTime \"UnloadTime\"     ");
            _sql.Append(" FROM TBLEMSACCESSORYSTATE A ");
            _sql.Append("   LEFT JOIN TBLEQPACCSTATECONTROL B ");
            _sql.Append("          ON A.AccessoryState = B.AccessoryState ");
            _sql.Append("         AND B.ControlType = 'Accessory' ");
            _sql.Append("   LEFT JOIN tblEQPAccessoryLife C ON A.AccessoryType = C.Accessory ");
            _sql.Append("   LEFT JOIN TBLEQPACCESSORYBASIS D ");
            _sql.Append("          ON A.AccessoryNo = D.AccessoryNo ");
            _sql.Append("         AND A.AccessoryVersion = D.AccessoryVersion ");
            _sql.Append("         AND A.AccessoryType = D.AccessoryType ");
            _sql.Append("   LEFT JOIN TBLEQPEQUIPMENTTYPEACCCATEGORY E ON D.AccessoryCategory = E.AccessoryCategory ");
            //20220308 add by 翰鴻 for M#102577
            _sql.Append("LEFT JOIN tblACCChangeTime G ON A.AccessoryNo = G.AccessoryNo AND A.AccessoryVersion = G.AccessoryVersion ");
            //20220307 add by 翰鴻 for M#102965
            //20220629 Modify by yvette for #115185:模治具於下模時，切換至換刀生產批時，顯示多筆重覆數據
            #region
            _sql.Append("LEFT JOIN (                                                                                    ");
            _sql.Append("  SELECT DISTINCT a.ACCESSORYNO                                                                ");
            _sql.Append("  FROM TBLWIPCONT_ACCESSORY a                                                                  ");
            _sql.Append("  JOIN TBLWIPLOTLOG b ON a.LOGGROUPSERIAL = b.LOGGROUPSERIAL                                   ");
            _sql.Append("  WHERE a.EQUIPMENTNO = '" + conten.EQUIPMENTNO + "'                                           ");
            _sql.Append("    AND EXISTS(                                                                                ");            
            _sql.Append("    SELECT *                                                                                   ");
            _sql.Append("    FROM tblWIPCont_Equipment aa                                                               ");
            _sql.Append("    JOIN tblWIPLotLog_Report bb ON aa.LOGGROUPSERIAL = bb.LOGGROUPSERIAL AND bb.STATUS = 0     ");
            _sql.Append("    JOIN tblWIPLotState cc ON bb.LOGGROUPSERIAL = cc.LOGGROUPSERIAL                            ");
            _sql.Append("    JOIN tblPRDProductParameters dd ON bb.PRODUCTNO = dd.PRODUCTNO                             ");
            _sql.Append("                                   AND bb.PRODUCTVERSION = dd.PRODUCTVERSION                   ");
            _sql.Append("                                   AND dd.RULENO = 'RULE_CHANGETOOL_BATCH'                     ");
            _sql.Append("    JOIN TBLOPBASIS ee ON bb.OPNO = ee.OPNO AND ee.PlugIn = 5                                  ");
            _sql.Append("    WHERE aa.EQUIPMENTNO = a.EQUIPMENTNO                                                       ");
            _sql.Append("    )                                                                                          ");
            _sql.Append("    AND EXISTS(                                                                                ");            
            _sql.Append("    SELECT *                                                                                   ");
            _sql.Append("    FROM TBLWIPLOTLOG_REPORT aa                                                                ");
            _sql.Append("    JOIN TBLWIPCONT_EQUIPMENT bb ON aa.LOGGROUPSERIAL = bb.LOGGROUPSERIAL                      ");
            _sql.Append("    JOIN TBLPRDPRODUCTPARAMETERS cc ON cc.PRODUCTNO = aa.PRODUCTNO                             ");
            _sql.Append("                                   AND cc.PRODUCTVERSION = aa.PRODUCTVERSION                   ");
            _sql.Append("                                   AND cc.ENABLE = 1                                           ");
            _sql.Append("                                   AND cc.RULENO = 'RULE_CHANGETOOL_BATCH'                     ");
            _sql.Append("    JOIN TBLOPBASIS ee ON aa.OPNO = ee.OPNO AND ee.PlugIn = 5                                  ");
            _sql.Append("    WHERE aa.STATUS = 1                                                                       ");
            _sql.Append("      AND EXISTS(                                                                              ");            
            _sql.Append("        SELECT *                                                                               ");
            _sql.Append("        FROM(                                                                                  ");
            _sql.Append("          SELECT bbb.EQUIPMENTNO, MAX(bbb.ENDTIME) ENDTIME                                     ");
            _sql.Append("          FROM TBLWIPLOTLOG_REPORT aaa                                                         ");
            _sql.Append("          JOIN TBLWIPCONT_EQUIPMENT bbb ON aaa.LOGGROUPSERIAL = bbb.LOGGROUPSERIAL             ");
            _sql.Append("          WHERE aaa.STATUS = 1                                                                 ");
            _sql.Append("          GROUP BY bbb.EQUIPMENTNO                                                             ");
            _sql.Append("        ) xx                                                                                   ");
            _sql.Append("        WHERE xx.ENDTIME = bb.ENDTIME                                                          ");
            _sql.Append("          AND xx.EQUIPMENTNO = bb.EQUIPMENTNO                                                   ");
            _sql.Append("      )                                                                                        ");
            _sql.Append("      AND bb.EQUIPMENTNO = a.EQUIPMENTNO                                                      ");
            _sql.Append("      AND aa.LOTNO = b.LOTNO                                                                     ");
            _sql.Append("  )                                                                                            ");
            _sql.Append(") F ON A.ACCESSORYNO = F.ACCESSORYNO                                                           ");
            #endregion
            if (is_auto_inout)
                _sql.Append(" WHERE UPPER(B.ControlNo) IN ('APPLY','INVENTORY')");
            else
                _sql.Append(" WHERE UPPER(B.ControlNo) = 'APPLY' ");
            _sql.Append("   AND E.EquipmentType = '" + conten.EQUIPMENTTYPE + @"' ");
            _sql.Append("   AND A.AccessoryNo NOT IN (SELECT DISTINCT AccessoryNo FROM tblEMSCombineACCState) ");
            
            //20221012 add by 翰鴻 for M#118366
            if(conten.ProductList.Count() > 0)
            {
                string str_ProductList = string.Empty;
                str_ProductList = string.Join("','", conten.ProductList);
                str_ProductList = "'" + str_ProductList + "'";
                
                _sql.Append("AND EXISTS(                                                        ");
                _sql.Append("    SELECT*                                                        ");
                _sql.Append("    FROM TBLPRDACCESSORY aa                                        ");
                _sql.Append("    WHERE (aa.ACCESSORYNO= '*' OR A.AccessoryNo= aa.ACCESSORYNO)   ");
                //_sql.Append("      AND(aa.PRODUCTNO IN(" + str_ProductList + ") OR NOT EXISTS(  ");
                //_sql.Append("        SELECT *                                                   ");
                //_sql.Append("        FROM TBLPRDACCESSORY                                       ");
                //_sql.Append("        WHERE PRODUCTNO IN(" + str_ProductList + ")                ");
                //_sql.Append("      ))                                                           ");
                // 20240311 modify by xingl for M#0159256: 点击上下具卡顿
                _sql.Append(" AND AA.PRODUCTNO IN(" + str_ProductList + ")");
                _sql.Append(" UNION");
                _sql.Append(" SELECT * FROM TBLPRDACCESSORY AA WHERE ( AA.ACCESSORYNO = '*' OR A.ACCESSORYNO = AA.ACCESSORYNO )");
                _sql.Append(" AND NOT EXISTS ( SELECT * FROM TBLPRDACCESSORY WHERE PRODUCTNO IN(" + str_ProductList + "))");
                _sql.Append("  )                                                                ");
            }            

            OutDataTable = CurrentService.GetDao().Query(_sql.ToString());
            
            OutDataTable.Columns.Add("CurrentMoldUps");

            //當沒有資料的時候需要給一筆空的 row 給H5
            if (OutDataTable.Rows.Count.Equals(0))
            {
                DataRow Insert_row = OutDataTable.NewRow();
                OutDataTable.Rows.Add(Insert_row);
            }
            else
            {
                // 查询上模次数
                _sql.Clear();
                _sql.Append(" select AccessoryNo,AccessoryVersion,CurrentMoldUps from TBLCUSMoldMoves ");
                List<ORM_TBLCUSMoldMoves> listCusMoldMoves = CurrentService.GetDao().Query<ORM_TBLCUSMoldMoves>(_sql.ToString());

                foreach (DataRow dr in OutDataTable.Rows)
                {
                    dr["CurrentMoldUps"] = "0";

                    var _cusMoves = listCusMoldMoves.Where(_ => _.AccessoryNo == dr["AccessoryNo"].ToString() && _.AccessoryVersion == dr["AccessoryVersion"].ToString());
                    if (_cusMoves != null)
                        if (_cusMoves.Count() > 0)
                            dr["CurrentMoldUps"] = _cusMoves.First().CurrentMoldUps.ToString();
                }
            }

            return OutDataTable;
        }
        //自動出入庫
        public bool get_auto_inout_val()
        {
            bool is_auto = false;
            string auto_val = CommonUtilities.GetParameterValue("EQPACCINVAutoInOut");
            if (string.IsNullOrEmpty(auto_val) == false)
            {
                if (auto_val.Equals("Y") || auto_val.Equals("1"))
                    is_auto = true;
            }
            return is_auto;
        }
    }
}
